CREATE PROCEDURE VENTAS_BUSCAR_MASCOTAS_L
@IDMASCOTA INTEGER,
@NOMBRE VARCHAR(18),
@GENERO VARCHAR(1),
@RAZA VARCHAR(20),
@IDCLIENTE INTEGER,
@ESTADOTRAN INTEGER OUTPUT
AS

--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

DECLARE @IDES INTEGER

BEGIN TRY
	--buscamos el id de la especie basado en la raza
	SET @IDES = (SELECT E.IDESPECIE
				 FROM ESPECIE E, RAZA R
				 WHERE R.DESCRIPCION = @RAZA AND
					   E.idRaza = R.idRaza)
	
	IF (@IDMASCOTA IS NULL) AND (@IDCLIENTE IS NULL) AND (@IDES IS NULL)
	BEGIN
		(SELECT * FROM MASCOTA
		WHERE NOMBRE LIKE @NOMBRE + '%' AND
		GENERO LIKE @GENERO + '%')
	END
			
	IF (@IDMASCOTA IS NULL) AND (@IDCLIENTE IS NULL) AND (@IDES IS NOT NULL)
	BEGIN
		(SELECT * FROM MASCOTA
		WHERE NOMBRE LIKE @NOMBRE + '%' AND
		GENERO LIKE @GENERO + '%' AND
		idEspecie = @IDES)
	END
		
	IF (@IDMASCOTA IS NULL) AND (@IDCLIENTE IS NOT NULL) AND (@IDES IS NULL)
	BEGIN
		(SELECT * FROM MASCOTA
		WHERE NOMBRE LIKE @NOMBRE + '%' AND
		GENERO LIKE @GENERO + '%' AND
		idCliente = @IDCLIENTE)
	END
			
	IF (@IDMASCOTA IS NULL) AND (@IDCLIENTE IS NOT NULL) AND (@IDES IS NOT NULL)
	BEGIN
		(SELECT * FROM MASCOTA
		WHERE NOMBRE LIKE @NOMBRE + '%' AND
		GENERO LIKE @GENERO + '%' AND
		idCliente = @IDCLIENTE AND
		idEspecie = @IDES)
	END
	
	IF (@IDMASCOTA IS NOT NULL) AND (@IDCLIENTE IS NULL) AND (@IDES IS NULL)
	BEGIN
		(SELECT * FROM MASCOTA
		WHERE NOMBRE LIKE @NOMBRE + '%' AND
		GENERO LIKE @GENERO + '%' AND
		idMascota = @IDMASCOTA)
	END
			
	IF (@IDMASCOTA IS NOT NULL) AND (@IDCLIENTE IS NULL) AND (@IDES IS NOT NULL)
	BEGIN
		(SELECT * FROM MASCOTA
		WHERE NOMBRE LIKE @NOMBRE + '%' AND
		GENERO LIKE @GENERO + '%' AND
		idEspecie = @IDES AND
		idMascota = @IDMASCOTA)
	END

	IF (@IDMASCOTA IS NOT NULL) AND (@IDCLIENTE IS NOT NULL) AND (@IDES IS NULL)
	BEGIN
		(SELECT * FROM MASCOTA
		WHERE NOMBRE LIKE @NOMBRE + '%' AND
		GENERO LIKE @GENERO + '%' AND
		idCliente = @IDCLIENTE AND
		idMascota = @IDMASCOTA)
	END
		
	IF (@IDMASCOTA IS NOT NULL) AND (@IDCLIENTE IS NOT NULL) AND (@IDES IS NOT NULL)
	BEGIN
		(SELECT * FROM MASCOTA
		WHERE NOMBRE LIKE @NOMBRE + '%' AND
		GENERO LIKE @GENERO + '%' AND
		idCliente = @IDCLIENTE AND
		idEspecie = @IDES AND
		idMascota = @IDMASCOTA)
	END
	
	SET @ESTADOTRAN = 1
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	SET @ESTADOTRAN = 0
	ROLLBACK TRANSACTION
END CATCH